﻿CREATE PROCEDURE [vantageexport].[s_Notes] AS
DECLARE @Pattern sysname SET @Pattern = '\d\d\d\D{0,2}\d\d\d\D{0,1}\d\d\d\d'
IF 1 = 1 BEGIN --[
TRUNCATE TABLE vantageexport.t_Note
INSERT INTO vantageexport.t_Note
    (NoteTypeID, NoteActionCodeID, StatusID, NoteDate, NoteDate0, NoteDate1, Note, StatusDate, StatusUser, VantageID, LoanID,Phone, PhoneCategoryID, 
    IsTask, NoteUser, IsResolved, IsCollection)
SELECT     TypeID, ISNULL(ActionCodeID, 201) AS NoteActionCodeID, StatusID, NoteDate, NoteDate0, NoteDate1, Notes, ISNULL(NoteDate, CreatedDate) 
                      AS StatusDate, CreatedUser, ExportID, LoanID, dbo.clr_RegEx_MatchGroup(Notes,'('+@Pattern+')',1) AS Phone, 0 AS PhoneCategoryID, 0 AS IsTask, REPLACE(CreatedUser,'Pat David','Patricia David') AS NoteUser, 1 AS IsResolved, 
                      CASE TypeID WHEN 1 THEN 1 ELSE 0 END AS IsCollection
FROM         (SELECT     N.AccountNumber, NT.RefID AS TypeID, NAT.RefID AS ActionCodeID, NR.RefID AS StatusID, N.NoteDate, dbo.Today(N.NoteDate) AS NoteDate0, 
                      dbo.Time(N.NoteDate) AS NoteDate1, N.Notes, N.CreatedDate, N.CreatedUser, N.ID AS ExportID, CF.LoanID
FROM         vantageexport.ts_NoteResultType AS NR INNER JOIN
                      vantageexport.ts_NoteActionType AS NAT INNER JOIN
                      vantage.t_Note AS N INNER JOIN
                      vantageexport.ts_NoteType AS NT ON N.NoteTypeRef = NT.Name ON NAT.Name = N.ActionTypeRef ON NR.Name = N.ResultTypeRef INNER JOIN
                      usix.v_CustomerFile_Export_List AS CF ON N.AccountNumber = CF.VantageID
WHERE     (N.CreatedUser <> 'Vantage Admin') AND (NOT (NT.RefID IN (24, 26))) AND (CF.LoanID > 0)) AS T

INSERT INTO vantageexport.t_Note
(NoteTypeID, NoteActionCodeID, StatusID, NoteDate, NoteDate0, NoteDate1, Note, StatusDate, StatusUser, VantageID, LoanID,Phone, PhoneCategoryID, 
    IsTask, NoteUser, IsResolved, IsCollection,DateDue,DateDueBy)
SELECT
NoteTypeID, NoteActionCodeID, StatusID, NoteDate, NoteDate0, NoteDate1, Note, StatusDate, REPLACE(StatusUser,'Pat David','Patricia David')StatusUser, VantageID, LoanID,Phone, PhoneCategoryID, 
    IsTask, REPLACE(NoteUser,'Pat David','Patricia David') NoteUser, IsResolved, IsCollection,DateDue,REPLACE(DateDueBy,'Pat David','Patricia David')DateDueBy
FROM vantageexport.v_Tasks WHERE LoanID >0

END --]

--SELECT LoanID,NoteDate FROM vantageexport.t_Note GROUP BY LoanID,NoteDate HAVING COUNT(*)>1
--RETURN
BEGIN TRAN
EXEC sp__Context_SecurityOff 1
EXEC sp__Context_ArchiveOff 1
EXEC sp__Context_WorkFlowOff 1
EXECUTE AS Login='AFC-HOME\SQLServer'

SELECT     Count,USERS.NoteUser, ISNULL(ADS.DisplayName,'Help Desk')DisplayName,dbo.StringMatch(USERS.NoteUser , ADS.DisplayName) Match
INTO #N_ADS
FROM         (SELECT NoteUser,COUNT(*) Count
                       FROM          vantageexport.t_Note GROUP BY NoteUser) AS USERS FULL OUTER JOIN
                      dbo.t_ADS_Login AS ADS ON dbo.StringMatch(USERS.NoteUser , ADS.DisplayName)>70
ORDER BY ADS.DisplayName

INSERT INTO dbo.t_Note(
LoanID,
Note,
StatusID,
StatusDate,
StatusUser,
DateDue,
DateDueBy,
NoteTypeID,
NoteActionCodeID,
Phone,
PhoneCategoryID,
NoteDate,
IsTask,
NoteUser,
IsResolved,
IsCollection,
VantageID
)
--SELECT LoanID,COUNT(*) Count FROM(
SELECT --TOP 10000
N.LoanID,
N.Note + CASE ADS.sAMAccountName WHEN 'help' THEN ':'+N.NoteUser ELSE '' END Note,
N.StatusID,
N.StatusDate,
ISNULL(ADS.sAMAccountName,'help') AS StatusUser,
N.NoteDate AS DateDue,
ISNULL(ADS.sAMAccountName,'help') AS DateDueBy,
N.NoteTypeID,
N.NoteActionCodeID,
N.Phone,
N.PhoneCategoryID,
N.NoteDate,
N.IsTask,
ISNULL(ADS.sAMAccountName,'help') AS NoteUser,
N.IsResolved,
N.IsCollection,
N.VantageID
FROM         vantageexport.t_Note AS N 
INNER JOIN #N_ADS N_ADS ON N_ADS.NoteUser = N.NoteUser
INNER JOIN dbo.t_ADS_Login AS ADS ON N_ADS.DisplayName = ADS.DisplayName
INNER JOIN (SELECT LoanID,NoteDate,MAX(ID)ID FROM vantageexport.t_Note GROUP BY LoanID,NoteDate)NN
	ON NN.ID = N.ID
LEFT OUTER JOIN dbo.t_Note NM ON NM.LoanID = N.LoanID AND NM.NoteDate = N.NoteDate
WHERE NM.LoanID IS NULL
--)T GROUP BY LoanID,NoteDate HAVING COUNT(*)>1

--SELECT     Count,USERS.NoteUser, ADS.DisplayName,dbo.StringMatch(USERS.NoteUser , ADS.DisplayName) Match
--FROM         (SELECT NoteUser,COUNT(*) Count
--                       FROM          vantageexport.t_Note GROUP BY NoteUser) AS USERS FULL OUTER JOIN
--                      t_ADS_Login AS ADS ON dbo.StringMatch(USERS.NoteUser , ADS.DisplayName)>70
--ORDER BY ADS.DisplayName
--SELECT TOP 2000 * FROM t_Note NOLOCK ORDER BY NoteDate DESC

DROP TABLE #N_ADS
IF @@ERROR = 0 COMMIT ELSE ROLLBACK
REVERT
